package com.csjy.migration.hnnd;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

public class DbUtil {

  private String driver;
  private String jdbcUrl;
  private DataSource ds;

  public String getDriver() {
    return driver;
  }

  public void setDriver(String driver) {
    this.driver = driver;
  }

  public String getJdbcUrl() {
    return jdbcUrl;
  }

  public void setJdbcUrl(String jdbcUrl) {
    this.jdbcUrl = jdbcUrl;
  }

  public DbUtil(Map<String,String> jdbcConfig) {
    this.driver = driver;
    this.jdbcUrl = jdbcUrl;
    HikariConfig config = new HikariConfig();
//    {"sourceJdbcUrl":"jdbc:oracle:thin:JWFRAME/1@localhost:1521:orcl","targetJdbcUrl":"jdbc:oracle:thin:HNND914/1@localhost:1521:orcl"}
    config.setJdbcUrl(jdbcConfig.get("url"));
    config.setDriverClassName(jdbcConfig.get("driverClassName"));
    config.setUsername(jdbcConfig.get("username"));
    config.setPassword(jdbcConfig.get("password"));
    if(jdbcConfig.get("maximumPoolSize") != null) {
      int max = Integer.valueOf(jdbcConfig.get("maximumPoolSize"));
      config.setMaximumPoolSize(max);
    }
    if(jdbcConfig.get("connectionTimeout(") != null) {
      config.setConnectionTimeout(Integer.parseInt(jdbcConfig.get("connectionTimeout")));
    }
    if(jdbcConfig.get("idleTimeout((") != null) {
      config.setIdleTimeout(Integer.parseInt(jdbcConfig.get("idleTimeout")));
    }
    this.ds = new HikariDataSource(config);
  }

  public static boolean testConn(Map<String,String> jdbcConfig) throws SQLException {
    Connection testConn = null;
    try {
      Class.forName(jdbcConfig.get("driverClassName"));
      testConn = DriverManager.getConnection(jdbcConfig.get("url"),jdbcConfig.get("username"),jdbcConfig.get("password"));
      return true;
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      if(testConn != null) {
        testConn.close();
      }
    }
    return false;
  }

  /**
   * 获取数据库连接
   * @return
   */
  public synchronized Connection getConn() throws SQLException {
    return ds.getConnection();
  }

  /**
   * 批量 更新/删除/插入数据
   * @param sql
   * @param params
   * @return
   * @throws SQLException
   */
  public synchronized int[] updateByParamsBatch(String sql, List<List> params, boolean isUpdate) throws SQLException {
    int[] result ;
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = this.ds.getConnection();
      conn.setAutoCommit(false);
      pstmt = conn.prepareStatement(sql);
      for (List values : params) {
        int index = 1;
        if (null != values && !values.isEmpty()) {
          if(isUpdate) {
            values.add(values.get(0));
            values.remove(0);
          }
          Iterator iterator= values.iterator();

          while(iterator.hasNext()){
            Object param = iterator.next();
            if(param instanceof InputStream) {
              pstmt.setBinaryStream(index++, (InputStream)param);
            } else {
              pstmt.setObject(index ++, param);
            }
          }
          pstmt.addBatch();
        }
      }
      result = pstmt.executeBatch();
      conn.commit();
      pstmt.close();
      conn.close();
    }catch(Exception e) {
      throw e;
    }finally {
      if(pstmt !=null) {
        pstmt.close();
      }

      if(conn !=null) {
        conn.close();
      }
    }
    return result;
  }

  /**
   * 更新/删除/插入数据
   * @param sql
   * @param params
   * @return
   * @throws SQLException
   */
  public synchronized boolean updateByParams(String sql, List params) throws SQLException {
    int result = -1;
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = this.ds.getConnection();
      conn.setAutoCommit(false);
      pstmt = conn.prepareStatement(sql);
      int index = 1;
      if (null != params && !params.isEmpty()) {
        for (int i = 0; i < params.size(); i ++) {
          if(params.get(i) instanceof InputStream) {
            pstmt.setBinaryStream(index++, (InputStream)params.get(i));
          } else {
            pstmt.setObject(index ++, params.get(i));
          }
        }
      }
      result = pstmt.executeUpdate();
      conn.commit();
      pstmt.close();
      conn.close();
    }catch(Exception e) {
      throw e;
    }finally {
      if(pstmt !=null) {
        pstmt.close();
      }
      if(conn !=null) {
        conn.close();
      }
    }
    return result > 0 ? true : false;
  }

  /**
   * 更新/删除/插入数据
   * @param sql
   * @param params
   * @return
   * @throws SQLException
   */
  public synchronized boolean updateByParams(String sql, List params, boolean isUpdate) throws SQLException {
    int result = -1;
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = this.ds.getConnection();
      conn.setAutoCommit(false);
      pstmt = conn.prepareStatement(sql);
      int index = 1;
      if (null != params && !params.isEmpty()) {
        if(isUpdate) {
          Object firstParam = params.get(0);
          params.add(firstParam);
          params.remove(0);
        }
        Iterator iterator= params.iterator();

        while(iterator.hasNext()){
          Object param = iterator.next();
          if(param instanceof InputStream) {
            pstmt.setBinaryStream(index++, (InputStream)param);
          } else {
            pstmt.setObject(index ++, param);
          }
        }
      }
      result = pstmt.executeUpdate();
      conn.commit();
      pstmt.close();
      conn.close();
    }catch(Exception e) {
      throw e;
    }finally {
      if(pstmt !=null) {
        pstmt.close();
      }
      if(conn !=null) {
        conn.close();
      }
    }
    return result > 0 ? true : false;
  }

  /**
   * 更新或插入
   * @param updateSql
   * @param insertSql
   * @param params
   * @return
   * @throws SQLException
   */
  public synchronized boolean insertOrUpdate(String updateSql,String insertSql,List params) throws SQLException {
    boolean updateResult = this.updateByParams(updateSql, params,true);
    if(updateResult) {
      return true;
    }
    return this.updateByParams(insertSql, params);
  }

  /**
   * 查询数据
   * @param sql
   * @param params
   * @return
   * @throws SQLException
   */
  public synchronized List<Map> selectByParams(String sql, List params) throws SQLException {
    List<Map> list = new ArrayList<Map>();
    int index = 1;
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = this.ds.getConnection();
      pstmt = conn.prepareStatement(sql);
      if (null != params && !params.isEmpty()) {
        for (int i = 0; i < params.size(); i ++) {
          pstmt.setObject(index++, params.get(i));
        }
      }
      rs = pstmt.executeQuery();
      ResultSetMetaData metaData = rs.getMetaData();
      while (rs.next()) {
        Map map = this.buildMapFromRow(rs,metaData);
        list.add(map);
      }
      pstmt.close();
      rs.close();
      conn.close();
    } catch (Exception e) {
      throw e;
    } finally {
      if(pstmt !=null) {
        pstmt.close();
      }
      if(rs !=null) {
        pstmt.close();
      }
      if(conn !=null) {
        conn.close();
      }
    }
    return list;
  }

  /**
   * 查询数据
   * @param sql
   * @param params
   * @return
   * @throws SQLException
   */
  public synchronized List<Map> selectByPagination(String sql, List params) throws SQLException {
    List<Map> list = new ArrayList<Map>();
    int index = 1;
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = this.ds.getConnection();
      pstmt = conn.prepareStatement(sql);
      pstmt.setFetchSize(Integer.MAX_VALUE);
      pstmt.setFetchDirection(ResultSet.FETCH_REVERSE);

      if (null != params && !params.isEmpty()) {
        for (int i = 0; i < params.size(); i ++) {
          pstmt.setObject(index++, params.get(i));
        }
      }
      rs = pstmt.executeQuery();
      ResultSetMetaData metaData = rs.getMetaData();
      while (rs.next()) {
        Map map = this.buildMapFromRow(rs,metaData);
        list.add(map);
      }
      pstmt.close();
      rs.close();
      conn.close();
    } catch (Exception e) {
      throw e;
    } finally {
      if(pstmt !=null) {
        pstmt.close();
      }
      if(rs !=null) {
        pstmt.close();
      }
      if(conn !=null) {
        conn.close();
      }
    }
    return list;
  }

  /**
   * 查询数据
   * @param sql
   * @param params
   * @return
   * @throws SQLException
   */
  public synchronized List<Object> selectSingleColumn(String sql, List params) throws SQLException {
    List<Object> list = new ArrayList<Object>();
    int index = 1;
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = this.ds.getConnection();
      pstmt = conn.prepareStatement(sql);
      if (null != params && !params.isEmpty()) {
        for (int i = 0; i < params.size(); i ++) {
          pstmt.setObject(index++, params.get(i));
        }
      }
      rs = pstmt.executeQuery();
      ResultSetMetaData metaData = rs.getMetaData();
      while (rs.next()) {
        list.add(rs.getObject(1));
      }
      pstmt.close();
      rs.close();
      conn.close();
    } catch (Exception e) {
      throw e;
    } finally {
      if(pstmt !=null) {
        pstmt.close();
      }
      if(rs !=null) {
        pstmt.close();
      }
      if(conn !=null) {
        conn.close();
      }
    }
    return list;
  }

  /**
   *  row=》map
   * @param rs
   * @param metaData
   * @return
   * @throws SQLException
   */
  public synchronized Map buildMapFromRow(ResultSet rs, ResultSetMetaData metaData) throws SQLException {
    Map row = new HashMap();
    for (int i = 0; i < metaData.getColumnCount(); i ++) {
      String cols_name = metaData.getColumnName(i + 1);
      Object cols_value = rs.getObject(cols_name);
      if (null == cols_value) {
        cols_value = "";
      }
      row.put(cols_name, cols_value);
    }
    return row;
  }

  public synchronized void insertMigrationIdMap(String sourceId, String targetId, String sourceName, String targetName,
                       String sourceKey, String targetKey, String category) throws SQLException {
    String sql = "INSERT INTO MIGRATION_ID_MAP\n" +
        "(SOURCE_ID, TARGET_ID, SOURCE_NAME, TARGET_NAME, SOURCE_KEY, TARGET_KEY, CATEGORY)\n" +
        "VALUES(?, ?, ?, ?, ?, ?, ?)\n";

    List values = new ArrayList();
    values.add(sourceId);
    values.add(targetId);
    values.add(sourceName);
    values.add(targetName);
    values.add(sourceKey);
    values.add(targetKey);
    values.add(category);
    this.updateByParams(sql,values);
  }

  /**
   * 记录到迁移id映射表 ,source_id 和 target_id 相同
   * @param sourceId
   * @throws SQLException
   */
  public synchronized void insertMigrationIdMap(String sourceId) throws SQLException {

    String sql = "INSERT INTO MIGRATION_ID_MAP\n" +
        "(SOURCE_ID, TARGET_ID, SOURCE_NAME, TARGET_NAME, SOURCE_KEY, TARGET_KEY, CATEGORY)\n" +
        "VALUES(?, ?, ?, ?, ?, ?, ?)\n";
    List values = new ArrayList();
    values.add(sourceId);
    values.add(sourceId);
    values.add(null);
    values.add(null);
    values.add(null);
    values.add(null);
    values.add(null);
    try {
      this.updateByParams(sql,values);
    } catch(Exception e) {
      // 判断异常是否是重复异常
      if(!e.getMessage().contains("MIGRATION_ID_MAP_UN")){
        throw e;
      }
    }
  }

  /**
   * 记录到迁移id映射表,会先做更新操作
   * @param sourceId
   * @param targetId
   * @throws SQLException
   */
  public synchronized void insertMigrationIdMap(String sourceId, String targetId) throws SQLException {
    // 先更新
    String updateSql = "UPDATE MIGRATION_ID_MAP SET SOURCE_ID = ? , TARGET_ID = ? WHERE SOURCE_ID = ? ";
    List updateValues = new ArrayList();
    updateValues.add(sourceId);
    updateValues.add(targetId);
    updateValues.add(sourceId);

    if(this.updateByParams(updateSql, updateValues)){
      return;
    }
    String sql = "INSERT INTO MIGRATION_ID_MAP\n" +
        "(SOURCE_ID, TARGET_ID, SOURCE_NAME, TARGET_NAME, SOURCE_KEY, TARGET_KEY, CATEGORY)\n" +
        "VALUES(?, ?, ?, ?, ?, ?, ?)\n";

    List values = new ArrayList();
    values.add(sourceId);
    values.add(targetId);
    values.add(null);
    values.add(null);
    values.add(null);
    values.add(null);
    values.add(null);
    this.updateByParams(sql,values);
  }
}
